from django.http import HttpResponse
from django.shortcuts import render
from selenium import webdriver
from lxml import etree
import time
import random
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.common.by import By
import pandas as pd
from bs4 import BeautifulSoup
import re
import requests
# from __future__ import unicode_literals
import json
import random


def hello(request):
#     zidian =''' [{
# 	"name": "南海诸岛","value": "2015"
# }, {
# 	"name": "北京","value": "2015"
# }]'''
    return HttpResponse()

def runoob(request):
    # context          = {}
    # context['hello'] = 'Hello World!'
    views_hello = "Hello World!"
    views_name = "菜鸟教程"
    views_list = ["菜鸟教程1","菜鸟教程2","菜鸟教程3"]
    return render(request,"runoob.html", {"hello":views_hello,"name":views_name,"views_list": views_list})

def home(request):
    List = ['吴老二博客', '渲染Json到模板']
    Dict = {'site': '小磊哥数据驾驶舱', 'author': '小磊哥',
            'time':'2020年6月20日','tymp':'测试'}
    print("欢迎访问数据驾驶舱！")
    print("开始爬虫！")
    headers = {
            "User-Agent":"Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/75.0.3770.100 Safari/537.36"
        }
    response = requests.get("http://www.baidu.com",headers = headers)
    dataList='''[
            {name:"南海诸岛",value:1020},
            {name: '北京', value: 1423},
            {name: '天津', value: 1102},
            {name: '上海', value: 1526},
            {name: '重庆', value: 452},
            {name: '河北', value: 357},
            {name: '河南', value: 230},
            {name: '云南', value: 463},
            {name: '辽宁', value: 1534},
            {name: '黑龙江', value: 753},
            {name: '湖南', value: 452},
            {name: '安徽', value: 145},
            {name: '山东', value: 153},
            {name: '新疆', value: 248},
            {name: '江苏', value: 653},
            {name: '浙江', value: 452},
            {name: '江西', value: 789},
            {name: '湖北', value: 999},
            {name: '广西', value: 999},
            {name: '甘肃', value: 999},
            {name: '山西', value: 555},
            {name: '内蒙古', value: 888},
            {name: '陕西', value: 555},
            {name: '吉林', value: 555},
            {name: '福建', value: 777},
            {name: '贵州', value: 452},
            {name: '广东', value: 352},
            {name: '青海', value: 456},
            {name: '西藏', value: 252},
            {name: '四川', value: 254},
            {name: '宁夏', value: 153},
            {name: '海南', value: 785},
            {name: '台湾', value: 152},
            {name: '香港', value: 452},
            {name: '澳门', value: 1523}
        ]'''
    
    ceshi = {}
    ceshi["ceshi"] = response
    print(response)
    return render(request, 'home.html', {
            'List': json.dumps(List),
            'Dict': json.dumps(Dict),
            'ceshi':json.dumps(Dict),
            'dataList':dataList
        })

def index(request):
    import pymysql.cursors
    import pymysql
    import pandas as pd
    from datetime import datetime
    from threading import Timer
    import time
    import sched
    import datetime
    import json
    num = -1
    # resu = []
    def shujus():
        num = -1
        def shuju():
            print("新一轮定时任务开启")
            print('TimeNow:%s' % (datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')))
            nonlocal num # 使用nonlocal 访问修改外部函数变量
            num += 1
            config = {
                'host':'127.0.0.1',
                'port':3306,#MySQL默认端口
                'user':'root',#mysql默认用户名
                'password':'123456789',
                'db':'world',#数据库
                'charset':'utf8mb4',
                'cursorclass':pymysql.cursors.DictCursor,
                }
            
            # 创建连接
            con= pymysql.connect(**config)
            # 执行sql语句
            try:
                
                with con.cursor() as cursor:
                    sql="select * from ditu"
                    cursor.execute(sql)
                    global result
                    result=cursor.fetchall() 
                    
                    sql_bingtu="select * from bingtu"
                    cursor.execute(sql_bingtu)
                    global bingtu_data
                    bingtu_data=cursor.fetchall() 
                    
                    sql_bingtu3="select * from bingtu3"
                    cursor.execute(sql_bingtu3)
                    global bingtu3_data
                    bingtu3_data=cursor.fetchall()
                    
                    sql_zzt2="select * from zhuzhuangtu2"
                    cursor.execute(sql_zzt2)
                    global zzt2_data
                    zzt2_data=cursor.fetchall()
                    
                    sql_jiage="select * from jiage"
                    cursor.execute(sql_jiage)
                    global jiage_data
                    jiage_data=cursor.fetchall()
            finally:
                con.close()
            df=pd.DataFrame(result)#转换成DataFrame格式
            df.head()
            # print(result)
            # for r in result:
            #    print(r)
            # print(result)
            global resu
            resu = result[num]
            # print(df)
            # for d in df:
            #    print(d)
            #    print(df[d])
                # print("[{"+d+":"+df[d][num]+"}]")
            # print(json.dumps(result))
            # num += 1
            # t = Timer(50, shuju)
            # t.start()
            print("第%s轮定时任务结束！！"%(num))
            # def timedTask():
            #     # 初始化 sched 模块的 scheduler 类
            #     scheduler = sched.scheduler(time.time, time.sleep)
            #     # 增加调度任务
            #     scheduler.enter(10, 1, shuju)
            #     # 运行任务
            #     scheduler.run()
            # # Timer(10, shuju, ()).start()
            # timedTask()
            # 每隔两秒执行一次任务
            return resu

        shuju()
        
        bingtu3_data
    shujus()
    return render(request,'index.html',{'ceshi':result,'bingtu_data':bingtu_data,'bingtu3_data':bingtu3_data,'zzt2':zzt2_data,'jiage':jiage_data})
    